package com.person.daoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.person.connection.DBConnection;
import com.person.dao.infer.AdminDao;
import com.person.vo.Article;
import com.person.vo.Comment;
public class AdminDaoImpl implements AdminDao {
	//获取数据库连接对象
	DBConnection dbc = new DBConnection();
	Connection conn = dbc.getConn();
	PreparedStatement psmt = null;
	ResultSet rs = null;
	String sql = null;
	int result = 0;
	
	/**
	 * 根据传入的用户名、密码，在数据库中进行查找，完成判断
	 * @param username 用户名
	 * @param password 密码
	 * @return 结果集
	 */
	@Override
	public ResultSet accntAndpassdVerif(String username, String password) {
		sql = "SELECT * FROM tb_admin WHERE admin_username=? AND admin_password=?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, username);
			psmt.setString(2, password);
			
			/*判断rs是否为空时，用.next()*/
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}

	/**
	 * 添加文章
	 * @param 需要添加的文章数据
	 * @return 返回是否添加成功
	 */
	@Override
	public boolean AddArticle(Article article) {
		sql = "INSERT INTO tb_article (article_title,article_content,article_descrip,a_id,article_date) VALUES(?,?,?,?,?)";
		boolean flag = false;
		//将Date转换为数据库中的类型
		java.sql.Timestamp  timeStamp= new Timestamp(article.getDate().getTime());
		try {
			psmt = conn.prepareStatement(sql);
//			psmt.setBinaryStream(1, article.getBackimage());
			psmt.setString(1, article.getTitle());
			psmt.setString(2, article.getContent());
			psmt.setString(3, article.getDecrip());
			psmt.setInt(4, article.getFk_id());
			psmt.setTimestamp(5, timeStamp);
			
			result = psmt.executeUpdate();
			if(result > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	/**
	 * 获取所有的文章数据
	 * @return 返回获取到的所有文章数据
	 */
	@Override
	public List<Article> getArticleAll() {
		sql = "SELECT * FROM tb_article";
		List<Article> lists = new ArrayList<Article>();
		
		try {
			psmt = conn.prepareStatement(sql);
			
			rs = psmt.executeQuery(); //返回的数据集合
			
			while(rs.next()) {
				Article arti = new Article();
				arti.setId(rs.getInt(1));
				arti.setBackimage(rs.getBinaryStream(2));
				arti.setTitle(rs.getString(3));
				arti.setContent(rs.getString(4));
				arti.setDecrip(rs.getString(5));
				arti.setFk_id(rs.getInt(6));
				arti.setDate(rs.getTimestamp(7));
				
				lists.add(arti);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return lists;
	}

	/**
	 * 根据传入文章的id查文章
	 * @param id 需要查询的文章的id
	 * @return 返回查询到的文章数据
	 */
	@Override
	public Article getArticleById(int id) {
		sql = "select * from tb_article where article_id=?";
		Article article = new Article();
		try {
			psmt = conn.prepareStatement(sql);
			
			psmt.setInt(1, id);
			rs = psmt.executeQuery();
			
			if(rs.next()) {
				article.setId(rs.getInt(1));
				article.setBackimage(rs.getBinaryStream(2));
				article.setTitle(rs.getString(3));
				article.setContent(rs.getString(4));
				article.setDecrip(rs.getString(5));
				article.setFk_id(rs.getInt(6));
				article.setDate(rs.getTimestamp(7));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return article;
	}

	/**
	 * 根据id修改文章
	 * @param arti 文章的信息，需要编辑的信息
	 * @return 返回是否编辑成功
	 */
	@Override
	public boolean articleModifyById(Article arti) {
		sql = "UPDATE tb_article SET article_title=?,article_content=?,article_descrip=? WHERE article_id=?";
		int num = 0;
		boolean flag = false;
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, arti.getTitle());
			psmt.setString(2, arti.getContent());
			psmt.setString(3, arti.getDecrip());
			psmt.setInt(4, arti.getId());
			
			num = psmt.executeUpdate();
			if(num > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	/**
	 * 根据文章id删除文章
	 * @param id 删除的文章id
	 * @return 返回是否删除成功
	 */
	@Override
	public boolean articleDeleteById(int id) {
		sql = "DELETE FROM tb_article WHERE article_id=?";
		int num = 0;
		boolean flag = false;
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			
			num = psmt.executeUpdate();
			if(num > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	/**
	 * 模糊查询文章标题
	 * @param title 模糊查询的文字
	 * @return 返回查询到的文章列表
	 */
	@Override
	public List<Article> articleFzzyQueryByTitle(String title) {
		sql = "SELECT * FROM tb_article WHERE article_title LIKE ?";
		List<Article> lists = new ArrayList<Article>();
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%" + title + "%");
			
			rs = psmt.executeQuery();
			
			while(rs.next()) {
				Article temparti = new Article();
				temparti.setId(rs.getInt(1));
				temparti.setTitle(rs.getString(2));
				temparti.setContent(rs.getString(3));
				temparti.setDecrip(rs.getString(4));
				temparti.setFk_id(rs.getInt(5));
				temparti.setDate(rs.getTimestamp(6));
				
				lists.add(temparti);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lists;
	}

	/**
	 * 文章列表分页功能
	 * 
	 * @param pageNum 指定的页码数
	 * @param pageSize 页的最大记录数
	 * @return 返回当前页的文章列表
	 */
	@Override
	public List<Article> articlePaging(int pageNum, int pageSize) {
		sql = "SELECT * FROM tb_article LIMIT ?,?";
		List<Article> lists = new ArrayList<Article>();
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, (pageNum - 1) * pageSize);
			psmt.setInt(2, pageSize);
			
			rs = psmt.executeQuery();
			while(rs.next()) {
				Article arti = new Article();
				arti.setId(rs.getInt(1));
				arti.setTitle(rs.getString(3));
				arti.setContent(rs.getString(4));
				arti.setDecrip(rs.getString(5));
				arti.setFk_id(rs.getInt(6));
				arti.setDate(rs.getTimestamp(7));
				lists.add(arti);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return lists;
	}

	/**
	 * 获取当前文章的总数
	 * @return 返回当前数据表中文章的总数
	 */
	@Override
	public int getArticleNum() {
		int artiSum = 0;
		sql = "SELECT COUNT(*) FROM tb_article";
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			rs.next();
			artiSum = rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return artiSum;
	}

	/**
	 * 模糊查询与分页功能合并
	 * @param pageNum 当前页码数
	 * @param pageSize 每页最大记录数
	 * @param title 模糊查询的文字
	 * @return 返回得到的文章列表
	 */
	@Override
	public List<Article> articlePagingAndFzzyQuery(int pageNum, int pageSize, String title, int userId) {
		sql = "SELECT * FROM tb_article WHERE article_title LIKE ? and a_id=? LIMIT ?,?";
		List<Article> lists = new ArrayList<>();
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%" + title + "%");
			psmt.setInt(2, userId);
			psmt.setInt(3, (pageNum - 1) * pageSize);
			psmt.setInt(4, pageSize);
			rs = psmt.executeQuery();
			
			while(rs.next()) {
				Article arti = new Article();
				arti.setId(rs.getInt(1));
				arti.setTitle(rs.getString(3));
				arti.setContent(rs.getString(4));
				arti.setDecrip(rs.getString(5));
				arti.setFk_id(rs.getInt(6));
				arti.setDate(rs.getTimestamp(7));
				lists.add(arti);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return lists;
	}

	/**
	 * 模糊查询的总记录数
	 * @return 返回得到的总记录数
	 */
	@Override
	public int articleFzzySum(String title, int userId) {
		sql = "SELECT COUNT(*) FROM tb_article WHERE article_title LIKE ? and a_id=?";
		
		int num = 0;
		
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, "%" + title + "%");
			psmt.setInt(2, userId);
			rs = psmt.executeQuery();
			
			if(rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return num;
	}

	/**
	 * 添加评论
	 */
	@Override
	public boolean commentAdd(Comment comment) {
		boolean flag = false;
		int num  = 0;
		if(comment.getWebsite() == null) {//判空
			comment.setWebsite("");
		}
		java.sql.Timestamp timeSql = new Timestamp(comment.getDate().getTime());
		
		sql = "INSERT INTO article_comment (content, nickname, email, website, datetime, praisenum, parent_id, article_id) VALUES(?,?,?,?,?,?,?,?)";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, comment.getCommtentCont());
			psmt.setString(2, comment.getNickName());
			psmt.setString(3, comment.getEmail());
			psmt.setString(4, comment.getWebsite());
			psmt.setTimestamp(5, timeSql);
			psmt.setInt(6, comment.getPraiseNum());
			psmt.setInt(7, comment.getParentId());
			psmt.setInt(8, comment.getArticleId());
			
			num = psmt.executeUpdate();
			if(num > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

	/**
	 * 根据文章id获取评论列表
	 */
	@Override
	public List<Comment> queryParentListById(int id) {
		sql = "select * from article_comment where article_id=?";
		List<Comment> lists = new ArrayList<>();
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			rs = psmt.executeQuery();
			while(rs.next()) {
				Comment comment = new Comment();
				comment.setCommtentId(rs.getInt(1));
				comment.setCommtentCont(rs.getString(2));
				comment.setNickName(rs.getString(3));
				comment.setEmail(rs.getString(4));
				comment.setWebsite(rs.getString(5));
				comment.setDate(new java.util.Date(rs.getTimestamp(6).getTime()));
				comment.setPraiseNum(rs.getInt(7));
				comment.setParentId(rs.getInt(8));
				comment.setArticleId(rs.getInt(9));
				lists.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lists;
	}
	
	/**
	 * 获取评论列表（所有评论记录）
	 */
	@Override
	public List<Comment> queryCommAll() {
		sql = "select * from article_comment";
		List<Comment> lists = new ArrayList<>();
		try {
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			while(rs.next()) {
				Comment comment = new Comment();
				comment.setCommtentId(rs.getInt(1));
				comment.setCommtentCont(rs.getString(2));
				comment.setNickName(rs.getString(3));
				comment.setEmail(rs.getString(4));
				comment.setWebsite(rs.getString(5));
				comment.setDate(new Date(rs.getTimestamp(6).getTime()));
				comment.setPraiseNum(rs.getInt(7));
				comment.setParentId(rs.getInt(8));
				comment.setArticleId(rs.getInt(9));
				lists.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lists;
	}
}
